﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ZX.DAL.DBExtend;
using System.Data.SqlClient;

namespace ZX.CRM.Bussiness
{
    public class BCrmReport
    {

        /// <summary>
        /// 获取所有客户信息
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnTableCustomer(DateTime begindate, DateTime enddate)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "select * from V_CrmCustomer a   where a.IsPrevate<>0";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取讲师佣金统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnTableTeachPay(string start, string end, string teacher, string customer)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "select * from V_ReportTeachPay";
            string strWhere = "";
            if (start != "")
            {
                strWhere = " where factpaydate >= '" + start + "'";
            }
            if (end != "")
            {
                if (strWhere != "")
                {
                    strWhere = strWhere + " and factpaydate<='" + end + "'";
                }
                else
                {
                    strWhere = " where factpaydate<='" + end + "'";
                }
            }
            if (teacher != "")
            {
                if (strWhere != "")
                {
                    strWhere = strWhere + " and TeachName like '%" + teacher + "%'";
                }
                else
                {
                    strWhere = " where TeachName like '%" + teacher + "%'";
                }
            }
            if (customer != "")
            {
                if (strWhere != "")
                {
                    strWhere = strWhere + " and CustomerName like '%" + customer + "%'";
                }
                else
                {
                    strWhere = " where CustomerName like '%" + customer + "%'";
                }
            }
            strsql = strsql + strWhere;
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取签到情况统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnDsMeetingSign(string courseName)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "";
            string strWhere = "";
            if (courseName != "")
            {
                strsql = @"select *,(select count(distinct m.CustomerName) from V_CrmDeliverMajorStudent m where m.IsSignIn=1 and m.OrderofDeptName=a.PartCompany 
                     and m.CourseLayoutName=a.CourseLayoutName) as AgainTrain  from (SELECT    CourseLayoutName,ISNULL( OrderofDeptName,'总计') AS PartCompany, CustomerName AS Boss, COUNT(DISTINCT CustomerName) AS BossName, COUNT (ClassName)  as sumnum,
                      sum (CASE WHEN IsSignIn = '1'  THEN 1 ELSE 0 END) as SignSumnum,
                      SUM(CASE IsTrain WHEN '1' THEN 0 ELSE 1 END) AS UnAgainTrain,SUM(CASE WHEN IsTrain = '0' AND 
                      IsDecision = '1' THEN 1 ELSE 0 END) AS UnAgainTrainBoss, SUM(CASE WHEN IsTrain = '1' AND IsDecision = '1' THEN 1 ELSE 0 END) 
                      AS AgainTrainBoss, SUM(CASE WHEN IsSignIn = '1' AND IsDecision = '1' THEN 1 ELSE 0 END) AS SianBoss, SUM(CASE WHEN IsSignIn = '1' AND 
                      IsTrain = '0' THEN 1 ELSE 0 END) AS UnAgainSureSian, SUM(CASE WHEN IsSignIn = '1' AND IsTrain = '1' THEN 1 ELSE 0 END) AS AgainSureSian, 
                      SUM(CASE WHEN IsSignIn = '1' AND IsTrain = '0' AND IsDecision = '1' THEN 1 ELSE 0 END) AS SureUnAgainSianBoss, 
                      SUM(CASE WHEN IsSignIn = '1' AND IsTrain = '1' AND IsDecision = '1' THEN 1 ELSE 0 END) AS SureAgainSianBoss, 
                      (CASE SUM(CASE WHEN IsDecision = '1' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE (SUM(CASE WHEN IsSignIn = '1' AND 
                      IsDecision = '1' THEN 1 ELSE 0 END) / CAST(SUM(CASE WHEN IsDecision = '1' THEN 1 ELSE 0 END) AS decimal)) END) AS BossPercent, 
                      SUM(CASE WHEN IsSignIn = '1' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS decimal) AS StudentPercent, MAX(AddTime) AS AddTime
                   FROM         dbo.V_CrmDeliverMajorStudent WHERE     (OrderofDeptName IS NOT NULL) and CourseLayoutName  like '%" + courseName + "%'  GROUP BY ROLLUP (OrderofDeptName,CourseLayoutName,CustomerName) )a where a.Boss is null and  CourseLayoutName is not null";
            }
            else
            {
                //default is null
                strsql = @"select *,(select count(distinct m.CustomerName) from V_CrmDeliverMajorStudent m where m.IsSignIn=1 and m.OrderofDeptName=a.PartCompany 
                     and m.CourseLayoutName=a.CourseLayoutName) as AgainTrain  from (SELECT    CourseLayoutName,ISNULL( OrderofDeptName,'总计') AS PartCompany, CustomerName AS Boss, COUNT(DISTINCT CustomerName) AS BossName, COUNT (ClassName)  as sumnum,
                      sum (CASE WHEN IsSignIn = '1'  THEN 1 ELSE 0 END) as SignSumnum,
                      SUM(CASE IsTrain WHEN '1' THEN 0 ELSE 1 END) AS UnAgainTrain,SUM(CASE WHEN IsTrain = '0' AND 
                      IsDecision = '1' THEN 1 ELSE 0 END) AS UnAgainTrainBoss, SUM(CASE WHEN IsTrain = '1' AND IsDecision = '1' THEN 1 ELSE 0 END) 
                      AS AgainTrainBoss, SUM(CASE WHEN IsSignIn = '1' AND IsDecision = '1' THEN 1 ELSE 0 END) AS SianBoss, SUM(CASE WHEN IsSignIn = '1' AND 
                      IsTrain = '0' THEN 1 ELSE 0 END) AS UnAgainSureSian, SUM(CASE WHEN IsSignIn = '1' AND IsTrain = '1' THEN 1 ELSE 0 END) AS AgainSureSian, 
                      SUM(CASE WHEN IsSignIn = '1' AND IsTrain = '0' AND IsDecision = '1' THEN 1 ELSE 0 END) AS SureUnAgainSianBoss, 
                      SUM(CASE WHEN IsSignIn = '1' AND IsTrain = '1' AND IsDecision = '1' THEN 1 ELSE 0 END) AS SureAgainSianBoss, 
                      (CASE SUM(CASE WHEN IsDecision = '1' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE (SUM(CASE WHEN IsSignIn = '1' AND 
                      IsDecision = '1' THEN 1 ELSE 0 END) / CAST(SUM(CASE WHEN IsDecision = '1' THEN 1 ELSE 0 END) AS decimal)) END) AS BossPercent, 
                      SUM(CASE WHEN IsSignIn = '1' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS decimal) AS StudentPercent, MAX(AddTime) AS AddTime
                      FROM         dbo.V_CrmDeliverMajorStudent
                      WHERE     (OrderofDeptName IS NOT NULL) and CourseLayoutName  like '123456'  GROUP BY ROLLUP (OrderofDeptName,CourseLayoutName,CustomerName) )a where a.Boss is null and  CourseLayoutName is not null";
            }
            strsql = strsql + strWhere;
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取签到情况统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnDsLivePay(string courseName)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "";
            string strWhere = "";
            if (courseName != "")
            {
                strsql = @"SELECT [DeptName]
                              ,[ID]
                              ,[CourseLayoutID]
                              ,[CustomerID]
                              ,[ResignMoney]
                              ,[TotalMoney]
                              ,[TotalRebate]
                              ,[PayType]
                              ,[POSNum]
                              ,[IsInvoice]
                              ,[InvoiceNum]
                              ,[PactNum]
                              ,[ReceiptNum]
                              ,[Explain]
                              ,[AddUser]
                              ,[AddTime]
                              ,[ProductPack]
                              ,[Caption]
                              ,[Packname]
                              ,[CustomerName]
                              ,[Amount]
                              ,[RebateMoney]
                              ,[AccountReceivable]
                              ,[ProductResignMoney]
                              ,[Money]
                              ,[Rebate]
                              ,[CustomerMgrName]
                              ,[ClassServerName]
                              ,[ClassFrontServerName]
                              ,[CourseLayoutName]
                              ,[BeginDate]
                              ,[EndDate]
                              ,[NegotiateTeachID]
                              ,[Place]
                              ,[NegotiateTeachName]
                               FROM [V_CrmLiveOrDerManagerProduct]  where  CourseLayoutName  like '%" + courseName + "%'";

            }
            strsql = strsql + strWhere;
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取签到情况统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnDsLivePaySum(string courseName)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "";
            string strWhere = "";
            if (courseName != "")
            {
                strsql = @"SELECT DeptName ,
                                  SUM(AccountReceivable)  as Tsum,
                                  SUM(RebateMoney)   as Rsum
                                  FROM [V_CrmLiveOrDerManagerProduct] 
                                  where  CourseLayoutName  like '%"+courseName+"%' group by  DeptName ";

            }
            strsql = strsql + strWhere;
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取讲师行程统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnDsTeachHodometer(string start, string end, string teacher, string customer)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "select * from V_ReportTeachHodometer";
            string strWhere = "";
            if (start != "")
            {
                strWhere = " where addtime >= '" + start + "'";
            }
            if (end != "")
            {
                if (strWhere != "")
                {
                    strWhere = strWhere + " and addtime<='" + end + "'";
                }
                else
                {
                    strWhere = " where addtime<='" + end + "'";
                }
            }
            if (teacher != "")
            {
                if (strWhere != "")
                {
                    strWhere = strWhere + " and TeachName like '%" + teacher + "%'";
                }
                else
                {
                    strWhere = " where TeachName like '%" + teacher + "%'";
                }
            }
            if (customer != "")
            {
                if (strWhere != "")
                {
                    strWhere = strWhere + " and CustomerName like '%" + customer + "%'";
                }
                else
                {
                    strWhere = " where CustomerName like '%" + customer + "%'";
                }
            }
            strsql = strsql + strWhere;
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取约场统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnTableCallResult(string begindate, string enddate)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "select DeptName,'Total(合计)' as SaleadName ,SUM(Num) as Num from (select DeptName,SaleadName,COUNT(DeptName)as Num from V_CrmCustomerOrder where AddTime between '" + begindate + "' and '" + enddate + "' group by SaleadName,DeptName) a group by DeptName union all select DeptName,SaleadName,COUNT(DeptName)as Num from V_CrmCustomerOrder where AddTime between '" + begindate + "' and '" + enddate + "' group by SaleadName,DeptName  order by DeptName,Num,SaleadName desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取订单统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnTableCustomerOrder(string begindate, string enddate, string strType)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "select * from V_CrmCustomerInfoOrder where OrderTypeName='" + strType + "' and AddTime between '" + begindate + "' and '" + enddate + "'";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取客户信息统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataTable ReturnResultCustomer(string begindate, string enddate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select  TypeName, COUNT(ID) as  typesum  from dbo.V_CrmCustomerInfo where customeermgr in(select  userid  from fn_GetOrganizeUser('E5D3214E-2745-4352-B120-C4F529F66013')) group by  TypeName  order  by TypeName  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取讲师统计结果                
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnTableTeachHodometer(string begindate, string enddate)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "select * from V_ReportTeachHodometer where HmDate between '" + begindate + "' and '" + enddate + "'";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);

            return dtcustomer;
        }
        /// <summary>
        /// 获取市场部客户结果        IsPrevate=1 and          
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet ReturnTableMagazine(string begindate, string enddate)
        {
            DataSet dtcustomer = new DataSet();
            string strsql = "select * from V_CrmReportCustomerInfo where AddTime between '" + begindate + "' and '" + enddate + "' ";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.Query(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有客户信息--客户类型
        /// </summary>         
        /// <returns></returns>
        public DataTable ReturnTableTypeNameDeptID(Guid deptid, int PowerType)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = string.Empty;

            //if (PowerType == 0)
            //    strsql = "select TypeId, TypeName, COUNT(ID) as  typesum,(select isnull(COUNT(b.ID),0)  from V_CrmTypeRecordInfo b left outer join "
            //              +" CrmCustomerInfo   cci  on cci.ID =b.CustomerId"
            //              +" where a.TypeName =b.NewTypeName and DATEDIFF(month, b.AddTime,GETDATE())=0"
            //              +" )as [newtype]    from dbo.V_CrmCustomerInfo a  "
            //              +" group by  TypeId, TypeName,PhraseValue  order   by  PhraseValue";
            //else
            //    strsql = strsql = " Select TypeId, TypeName, COUNT(ID) as  typesum,(select isnull(COUNT(b.ID),0)  from V_CrmTypeRecordInfo b left outer join "
            //             +"  CrmCustomerInfo   cci  on cci.ID =b.CustomerId"
            //            +"  Where a.TypeName =b.NewTypeName and DATEDIFF(month, b.AddTime,GETDATE())=0"
            //             +"  And cci.CustomeerMgr in (select  userid  from fn_GetOrganizeUser('" + deptid  + "'))"
            //             +"  )as [newtype]    from dbo.V_CrmCustomerInfo a where CustomUser "
            //             +"  In (select  userid  from Fn_GetOrganizeUser('" + deptid + "'))"
            //             +"  Group by  TypeId, TypeName,PhraseValue  order   by  PhraseValue "; 
            if (PowerType == 0)
                strsql = "select  t.TypeId,t.TypeName,t.typesum,ISNULL(y.MonthNum,0) AS [MonthNum],ISNULL(y.QqNum,0)  AS [QqNum],ISNULL(y.YearNum,0)  AS [YearNum]  from  (select TypeId, TypeName, isnull(COUNT(ID),0) as  typesum    from dbo.V_CrmCustomerInfo a  "
                         +" group by  TypeId, TypeName,PhraseValue ) t  left outer join    "                      
                         +" (select *  from ( select TypeId,SUM(MonthNum)  as [MonthNum], SUM(r.QqNum) AS [QqNum],SUM(R.YearNum) AS [YearNum]  from ("
                         + " select TypeId,   case   when DATEDIFF(month,b.Addtime,GETDATE())=0 then isnull(COUNT(distinct b.ID),0)  else 0 end AS [MonthNum],"
                         + "   case   when DATEDIFF(QQ ,b.Addtime,GETDATE())=0 then isnull(COUNT(distinct b.ID),0)    else 0 end AS [QqNum],"
                         + " 			  case   when DATEDIFF(YEAR,b.Addtime,GETDATE())=0 then isnull(COUNT(distinct b.ID),0)   else 0 end  AS [YearNum]"
						 +" 	from V_CrmTypeRecordInfo b left outer join  CrmCustomerInfo  cci on cci.ID =b.CustomerId"
						 +" 	group by TypeId ,b.Addtime)  r"
						 +" 	group by  TypeId) p    "                          
                         +" )as    y on y.TypeId =t.TypeId"
                         +" group by   t.TypeId,t.TypeName,t.typesum,y.MonthNum,y.QqNum,y.YearNum"
                         + " ORDER BY   t.TypeName";
            else
                strsql = strsql = " select t.TypeId, t.TypeName,t.typesum,ISNULL(y.MonthNum,0) AS [MonthNum],ISNULL(y.QqNum,0)  AS [QqNum],ISNULL(y.YearNum,0)  AS [YearNum]  from  ("
                         +" select TypeId, TypeName, isnull(COUNT(ID),0) as  typesum    from dbo.V_CrmCustomerInfo a "
                         +" where CustomeerMgr in  (select  userid  from Fn_GetOrganizeUser('"+deptid+"'))"
                         +" group by  TypeId, TypeName,PhraseValue ) t  left outer join   "                       
                         +" (select *  from ( select TypeId,SUM(MonthNum)  as [MonthNum], SUM(r.QqNum) AS [QqNum],SUM(R.YearNum) AS [YearNum]  from ("
                         + "	select TypeId,   case   when DATEDIFF(month,b.Addtime,GETDATE())=0 then isnull(COUNT(distinct b.ID),0)  else 0 end AS [MonthNum],"
                        + "			  case   when DATEDIFF(QQ ,b.Addtime,GETDATE())=0 then isnull(COUNT(distinct b.ID),0)    else 0 end AS [QqNum],"
                        + "			  case   when DATEDIFF(YEAR,b.Addtime,GETDATE())=0 then isnull(COUNT(distinct b.ID),0)   else 0 end  AS [YearNum]"
						+"	from V_CrmTypeRecordInfo b left outer join  CrmCustomerInfo  cci on cci.ID =b.CustomerId"
                        + "	where cci.CustomeerMgr in (select  userid  from Fn_GetOrganizeUser('" + deptid + "'))"
						+"	group by TypeId ,b.Addtime)  r"
						+"	group by  TypeId) p    "                          
                        +" )as    y on y.TypeId =t.TypeId"
                        +" group by   t.TypeId, t.TypeName,t.typesum,y.MonthNum,y.QqNum,y.YearNum"
                        + " ORDER BY  t.TypeName"; 
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
 
        public DataTable ReturnTableInnerTeach(int  type,DateTime StartDate, DateTime EndDate)
        {
            DataTable dtInnerTeach = new DataTable();
            string strsql = string.Empty;

            if (type == 0)
                strsql = "select InnerTeachDeptName,innerteachName,InnerEmpCode,"
                        +" sum(case  when (welldegree between 90 and  100) then 1 else 0 end) as '90-100',"
                        +" sum(case  when (welldegree between 80 and  89) then 1 else 0 end) as '80-89',"
                        +" sum (case  when (welldegree between 70 and  79) then 1 else 0 end) as '70-79',"
                        +" sum (case  when (welldegree between 60 and  69) then 1 else 0 end) as '60-69',"
                        +"sum (case  when ( welldegree<60) then 1 else 0 end) as '60以下',"
                        + " COUNT (ID) AS 'SUM' from dbo.V_CrmRptDeliverTrain   where  EndDate between '" + StartDate + "' and '" + EndDate + "'  and DliverState='20f825c4-292a-4386-8157-923668bd2682'"
                        + " and WellDegree is not null GROUP BY InnerTeachDeptName,innerteachName,InnerEmpCode ";
            else
                strsql = "select InnerTeachDeptName,innerteachName,InnerEmpCode,"
                        + " sum(case  when (SucceedDegree between 90 and  100) then 1 else 0 end) as '90-100',"
                        + " sum(case  when (SucceedDegree between 80 and  89) then 1 else 0 end) as '80-89',"
                        + " sum (case  when (SucceedDegree between 70 and  79) then 1 else 0 end) as '70-79',"
                        + " sum (case  when (SucceedDegree between 60 and  69) then 1 else 0 end) as '60-69',"
                        + " sum (case  when ( SucceedDegree<60) then 1 else 0 end) as '60以下',"
                        + " COUNT (ID) AS 'SUM' from dbo.V_CrmRptDeliverTrain "
                        + " where  EndDate between '" + StartDate + "' and '" + EndDate + "'  and DliverState='20f825c4-292a-4386-8157-923668bd2682' and SucceedDegree is not null GROUP BY  InnerTeachDeptName,innerteachName,InnerEmpCode";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtInnerTeach = execsql.ExecuteQuery(strsql);
            return dtInnerTeach;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public DataTable ReturnSumInnerTeach(int type, DateTime StartDate, DateTime EndDate)
        {

            DataTable dtInnerTeach = new DataTable();
            string strsql = string.Empty;
            if(type == 0)

              strsql = " select  name,sum from"   
                      +"( select             sum(case  when (a.welldegree between 90 and  100) then 1 else 0 end) as [90-100],"
                      +"sum(case  when (a.welldegree between 80 and  89) then 1 else 0 end) as [80-89],"
                      +"sum (case  when (a.welldegree between 70 and  79) then 1 else 0 end) as [70-79],"
                      +"sum (case  when (a.welldegree between 60 and  69) then 1 else 0 end) as [60-69] ,"
                      +"sum (case  when ( a.welldegree<60) then 1 else 0 end) as [60以下]"
                      + "from dbo.V_CrmRptDeliverTrain a where EndDate  between '" + StartDate + "'  and  '" + EndDate + "' and DliverState='20f825c4-292a-4386-8157-923668bd2682' and welldegree is not null ) a   "
                      +"unpivot"
                      +"("    
                      +"sum for name in ([90-100],[80-89],[70-79],[60-69],[60以下])"
                      +")as  ff";
            else
                strsql = " select  name,sum from"
                        + "( select             sum(case  when (a.SucceedDegree between 90 and  100) then 1 else 0 end) as [90-100],"
                        + "sum(case  when (a.SucceedDegree between 80 and  89) then 1 else 0 end) as [80-89],"
                        + "sum (case  when (a.SucceedDegree between 70 and  79) then 1 else 0 end) as [70-79],"
                        + "sum (case  when (a.SucceedDegree between 60 and  69) then 1 else 0 end) as [60-69] ,"
                        + "sum (case  when ( a.SucceedDegree<60) then 1 else 0 end) as [60以下]"
                        + "from dbo.V_CrmRptDeliverTrain a where EndDate  between '" + StartDate + "'  and  '" + EndDate + "' and DliverState='20f825c4-292a-4386-8157-923668bd2682' and SucceedDegree is not null ) a   "
                        + "unpivot"
                        + "("
                        + "sum for name in ([90-100],[80-89],[70-79],[60-69],[60以下])"
                        + ")as  ff";

            DbSql execsql = new DAL.DBExtend.DbSql();
            dtInnerTeach = execsql.ExecuteQuery(strsql);
            return dtInnerTeach;
        }

        /// <summary>
        /// 获取所有客户信息--客户类型
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableTypeName(Guid UserID, int PowerType)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = string.Empty;

           // if (PowerType == 0)
           //     strsql = "select TypeId, TypeName, COUNT(ID) as  typesum,(select isnull(COUNT(b.ID),0)  from V_CrmTypeRecordInfo b left outer join "
           //               +" CrmCustomerInfo   cci  on cci.ID =b.CustomerId"
           //               +" where a.TypeName =b.NewTypeName and DATEDIFF(month, b.AddTime,GETDATE())=0"
           //               +" )as [newtype]    from dbo.V_CrmCustomerInfo a  "
           //               +" group by  TypeId, TypeName,PhraseValue  order   by  PhraseValue ";
           // else
           //     strsql = " Select TypeId, TypeName, COUNT(ID) as  typesum,(select isnull(COUNT(b.ID),0)  from V_CrmTypeRecordInfo b left outer join "
           //              + "  CrmCustomerInfo   cci  on cci.ID =b.CustomerId"
           //              + "  Where a.TypeName =b.NewTypeName and DATEDIFF(month, b.AddTime,GETDATE())=0"
           //              + "  And cci.CustomeerMgr in('" + UserID + "')"
           //              + "  )as [newtype]    from dbo.V_CrmCustomerInfo a where CustomUser "
           //              + "  In ('" + UserID + "')"
           //              + "  Group by  TypeId, TypeName,PhraseValue  order   by  PhraseValue   ";
            if (PowerType == 0)
                strsql = "select t.TypeId, t.TypeName,t.typesum,ISNULL(y.MonthNum,0) AS [MonthNum],ISNULL(y.QqNum,0)  AS [QqNum],ISNULL(y.YearNum,0)  AS [YearNum]  from  (select TypeId, TypeName, isnull(COUNT(ID),0) as  typesum    from dbo.V_CrmCustomerInfo a  "
                         + " group by  TypeId, TypeName,PhraseValue ) t  left outer join    "
                         + " (select *  from ( select TypeId,SUM(MonthNum)  as [MonthNum], SUM(r.QqNum) AS [QqNum],SUM(R.YearNum) AS [YearNum]  from ("
                         + " select TypeId,   case   when DATEDIFF(month,b.Addtime,GETDATE())=0 then isnull(COUNT(b.ID),0)  else 0 end AS [MonthNum],"
                         + "   case   when DATEDIFF(QQ ,b.Addtime,GETDATE())=0 then isnull(COUNT(b.ID),0)    else 0 end AS [QqNum],"
                         + " 			  case   when DATEDIFF(YEAR,b.Addtime,GETDATE())=0 then isnull(COUNT(b.ID),0)   else 0 end  AS [YearNum]"
                         + " 	from V_CrmTypeRecordInfo b left outer join  CrmCustomerInfo  cci on cci.ID =b.CustomerId"
                         + " 	group by TypeId ,b.Addtime)  r"
                         + " 	group by  TypeId) p    "
                         + " )as    y on y.TypeId =t.TypeId"
                         + " group by  t.TypeId, t.TypeName,t.typesum,y.MonthNum,y.QqNum,y.YearNum"
                         + " ORDER BY    t.TypeName";
            else
                strsql = strsql = " select t.TypeId, t.TypeName,t.typesum,ISNULL(y.MonthNum,0) AS [MonthNum],ISNULL(y.QqNum,0)  AS [QqNum],ISNULL(y.YearNum,0)  AS [YearNum]  from  ("
                         + " select TypeId, TypeName, isnull(COUNT(ID),0) as  typesum    from dbo.V_CrmCustomerInfo a "
                         + " where CustomeerMgr in  ('" + UserID + "')"
                         + " group by  TypeId, TypeName,PhraseValue ) t  left outer join   "
                         + " (select *  from ( select TypeId,SUM(MonthNum)  as [MonthNum], SUM(r.QqNum) AS [QqNum],SUM(R.YearNum) AS [YearNum]  from ("
                         + "	select TypeId,   case   when DATEDIFF(month,b.Addtime,GETDATE())=0 then isnull(COUNT(b.ID),0)  else 0 end AS [MonthNum],"
                        + "			  case   when DATEDIFF(QQ ,b.Addtime,GETDATE())=0 then isnull(COUNT(b.ID),0)    else 0 end AS [QqNum],"
                        + "			  case   when DATEDIFF(YEAR,b.Addtime,GETDATE())=0 then isnull(COUNT(b.ID),0)   else 0 end  AS [YearNum]"
                        + "	from V_CrmTypeRecordInfo b left outer join  CrmCustomerInfo  cci on cci.ID =b.CustomerId"
                        + "	where cci.CustomeerMgr in ('" + UserID + "')"
                        + "	group by TypeId ,b.Addtime)  r"
                        + "	group by  TypeId) p    "
                        + " )as    y on y.TypeId =t.TypeId"
                        + " group by   t.TypeId,t.TypeName,t.typesum,y.MonthNum,y.QqNum,y.YearNum"
                        + " ORDER BY   t.TypeName"; 

            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }


        /// <summary>
        /// 获取所有客户信息--客户行业
        /// </summary>         
        /// <returns></returns>
        public DataTable ReturnTableTradeNameDeptID(Guid deptid)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select top 30  TradeName, COUNT(ID) as  typesum  from dbo.V_CrmCustomerInfo where CustomUser in(select  userid  from fn_GetOrganizeUser('" + deptid.ToString() + "')) group by TradeName  order  by typesum desc, TradeName  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }

        /// <summary>
        /// 获取所有分公司回款金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableBackMoneyDept(string DeptID, string type, int iMonth, int iYear, int iNMonth, int iNYear, string tempFw)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            string strcon = "";
            if (tempFw == "yes")
            {
                if (iNMonth == 12)
                {
                    iNYear = iNYear + 1;
                    iNMonth = 0;
                }
                strcon = "Date>='" + iYear + "-" + iMonth + "-1' and Date<='" + iNYear + "-" + (iNMonth + 1) + "-1'";
            }
            else {
                strcon = "DATEPART(YEAR,Date)=" + iYear + " AND DATEPART(MONTH,Date)=" + iMonth;
            }
            if (DeptID == "")
            {
                strsql = "select top 25 LEFT(deptName,2) as deptName,SUM(OrderResignMoney) as backPay,min(Date) from V_CrmCustomerInfoOrders where " + strcon + "  group by LEFT(deptName,2) order by backPay desc";
            }
            else
            {
                if (type == "1")
                {
                    strsql = "select distinct CustomerMgr as deptName , SUM(OrderResignMoney) as backPay,min(Date) from V_CrmCustomerInfoOrders where CustomeerMgr='" + DeptID + "' and " + strcon + " group by CustomerMgr";
                }
                else if (type == "2")//团队   a2ac801f-bc66-4911-b021-56288c51e04a'))
                {
                    strsql = "select distinct CustomerMgr as deptName , SUM(OrderResignMoney) as backPay,min(Date) from V_CrmCustomerInfoOrders where CustomeerMgr in(select userid from datuser where empid in (select ID from hrEmployee where DepartmentID='" + DeptID + "')) and " + strcon + " group by CustomerMgr";
                }
                else
                {
                    strsql = "select dd.DeptName as deptName ,SUM(OrderResignMoney) as backPay,min(Date)  from DatDepartment dd INNER JOIN DatDepartment dd1 ON dd.ParentID=dd1.DeptID INNER JOIN hrEmployee e ON dd.DeptID=e.DepartmentID AND dd1.ParentID='" + DeptID + "' INNER JOIN DatUser du ON du.EmpID=e.ID INNER JOIN V_CrmCustomerInfoOrders co ON du.UserID=co.CustomeerMgr where DATEPART(YEAR,Date)=" + iYear + " AND DATEPART(MONTH,co.Date)=" + iMonth + " group by dd.DeptName";
                }
            } DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有分公司回款金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableAllBackMoneyDept(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type == "0")
            {
                strsql += "select top 24 substring(d.DeptName,0,3) as  [DeptName],  CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [Rsum]"
                       + "  from CrmPay a left outer join "
                       + "  CrmProductPay b on b.PayId =a.ID left outer join  DatDepartment d on a.DeptId = d.DeptID"
                       + " where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and   FactPayDate  between  '" + StartDate + "' and  '" + EndDate + "' group by  d.DeptName "
                       + " order by [Rsum] desc";
            }
            else
            {
                strsql += "select top 24   substring(d.DeptName,0,3) as  [DeptName],  CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [Rsum]"
                         + "  from CrmPay a left outer join "
                         + "  CrmProductPay b on b.PayId =a.ID left outer join  DatDepartment d on a.DeptId = d.DeptID"
                         + " where  a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and     FactPayDate  between  '" + StartDate + "' and  '" + EndDate + "' group by  d.DeptName "
                         + " order by [Rsum] desc";

            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }

        /// <summary>
        /// 获取所有分公司同比环比回款金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnComparisonAllBackMoney(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
           

            int Year = StartDate.Year - EndDate.Year;

            int Month = Year * 12 + (StartDate.Month - EndDate.Month);
 


            string strsql = "";

            strsql += "select top 24  substring(d.DeptName,0,3) as  [DeptName],"
                       + "CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [Sums],"
                       +"("
                       + "	select  CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  "
                       +"	from CrmPay a1 left outer join "
                       +"              CrmProductPay b on b.PayId =a1.ID left outer join "
                       +"              DatDepartment d1 on a1.DeptId = d1.DeptID"
                       + "   where a1.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  d1.DeptName =d.DeptName and a1.FactPayDate  between  '" + StartDate.AddMonths(Month) + "'  and '" + EndDate.AddMonths(Month) + "'"	
                       +") as [PMSum], "
                       +"("
                       + "	select  CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  "
                       +"	from CrmPay a2 left outer join "
                       +"              CrmProductPay b on b.PayId =a2.ID left outer join "
                       +"              DatDepartment d2 on a2.DeptId = d2.DeptID"
                       + "   where a2.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and d2.DeptName =d.DeptName and a2.FactPayDate  between  '" + StartDate.AddYears(-1) + "'  and '" + EndDate.AddYears(-1) + "'"	
                       +" ) as [PYSum]"
                       +" from CrmPay a left outer join "
                       +"              CrmProductPay b on b.PayId =a.ID left outer join  "
                       +"              DatDepartment d on a.DeptId = d.DeptID"
                       + "  where a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and a.FactPayDate  between  '" + StartDate + "'  and '" + EndDate+ "' group by  d.DeptName"
                       +" order by [Sums] desc";
           
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有团队回款同比环比金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnComparisonAllTermBackMoney(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();

            int Year = StartDate.Year - EndDate.Year;

            int Month = Year * 12 + (StartDate.Month - EndDate.Month);
            string strsql = "";
            if (type == "0")
            {
                strsql += " select top 20 datDepartment_1.DeptName as  [DeptName],CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [Sums]"
                        +" ,"
                        +" ( "
                        + " select CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  "
                        +" from CrmPay a1 left outer join "
                        +" CrmProductPay b on b.PayId =a1.ID left outer join "
                        +" DatDepartment d on a1.DeptId = d.DeptID left outer join"
                        +" DatUser    du  on a1.AddUser =du.UserID  left  outer join "
                        +" DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                        +" DatDepartment as datDepartment_2  on  datDepartment_2.DeptID=de.DeptID"
                        + " where a1.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and datDepartment_2.DeptName =datDepartment_1.DeptName   "
                        +"        and"
                        + "        FactPayDate  between  '" + StartDate.AddMonths(Month) + "'  and '" + EndDate.AddMonths(Month) + "'"
                      
                        +" ) as [PMSum],"
                        +" ( "
                        + " select CAST(round(cast(Sum(PayMoney) as decimal(9,2))/10000,3)  as  numeric(9,2))  "
                        +" from CrmPay a2 left outer join "
                        +" CrmProductPay b on b.PayId =a2.ID left outer join "
                        +" DatDepartment d on a2.DeptId = d.DeptID left outer join"
                        +" DatUser    du  on a2.AddUser =du.UserID  left  outer join "
                        +" DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                        +" DatDepartment as datDepartment_3  on  datDepartment_3.DeptID=de.DeptID"
                        + " where a2.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  datDepartment_3.DeptName =datDepartment_1.DeptName "
                        +"        and "
                        + "        FactPayDate  between  '" + StartDate.AddYears(-1) + "'  and '" + EndDate.AddYears(-1) + "'"                     
                        +" ) as [PYSum] "                   
                    
                       +" from CrmPay a left outer join "
                        +" CrmProductPay b on b.PayId =a.ID left outer join" 
                        +" DatDepartment d on a.DeptId = d.DeptID left outer join"
                        +" DatUser    du  on a.AddUser =du.UserID  left  outer join "
                        +" DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                        +" DatDepartment as datDepartment_1  on  datDepartment_1.DeptID=de.DeptID"
                        + " where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and    FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'"
                        +" group by  datDepartment_1.DeptName"
                        +" order by [Sums]  desc";
            }
            else 
            {
                strsql += " select top 20 datDepartment_1.DeptName as  [DeptName],CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [Sums]"
                        + " ,"
                        + " ( "
                        + " select CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  "
                        + " from CrmPay a1 left outer join "
                        + " CrmProductPay b on b.PayId =a1.ID left outer join "
                        + " DatDepartment d on a1.DeptId = d.DeptID left outer join"
                        + " DatUser    du  on a1.AddUser =du.UserID  left  outer join "
                        + " DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                        + " DatDepartment as datDepartment_2  on  datDepartment_2.DeptID=de.DeptID"
                        + " where a1.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  datDepartment_2.DeptName =datDepartment_1.DeptName   "
                        + "        and"
                        + "        FactPayDate  between '" + StartDate.AddMonths(Month) + "'  and '" + EndDate.AddMonths(Month) + "'"

                        + " ) as [PMSum],"
                        + " ( "
                        + " select CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  "
                        + " from CrmPay a2 left outer join "
                        + " CrmProductPay b on b.PayId =a2.ID left outer join "
                        + " DatDepartment d on a2.DeptId = d.DeptID left outer join"
                        + " DatUser    du  on a2.AddUser =du.UserID  left  outer join "
                        + " DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                        + " DatDepartment as datDepartment_3  on  datDepartment_3.DeptID=de.DeptID"
                        + " where a2.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  datDepartment_3.DeptName =datDepartment_1.DeptName "
                        + "        and "
                        + "        FactPayDate  between  '" + StartDate.AddYears(-1) + "'  and '" + EndDate.AddYears(-1) + "'"
                        + " ) as [PYSum] "

                       + " from CrmPay a left outer join "
                        + " CrmProductPay b on b.PayId =a.ID left outer join"
                        + " DatDepartment d on a.DeptId = d.DeptID left outer join"
                        + " DatUser    du  on a.AddUser =du.UserID  left  outer join "
                        + " DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                        + " DatDepartment as datDepartment_1  on  datDepartment_1.DeptID=de.DeptID"
                        + " where a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  a.AddUser in (select   userid from  dbo.fn_GetOrganizeUser('" + DeptID + "')) and    FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'"
                        + " group by  datDepartment_1.DeptName"
                        + " order by [Sums]  desc";

            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有团队回款金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableAllTeamBackMoney(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
           // if (type == "0")
            //{
              //  strsql += " select top 20 datDepartment_1.DeptName as  [DeptName],  CAST(round(cast(Sum(PayMoney) as decimal(9,2))/10000,3)  as  numeric(9,2))   as [Rsum]"
             //          + "   from CrmPay a left outer join "
             //          + "   CrmProductPay b on b.PayId =a.ID left outer join "
             //          + "   DatDepartment d on a.DeptId = d.DeptID left outer join"
             //          + "   DatUser    du  on a.AddUser =du.UserID  left  outer join "
            //           + "   DatEmployee de on du.EmpID =de.EmpID  left outer  join"
            //           + "   DatDepartment as datDepartment_1  on  datDepartment_1.DeptID=de.DeptID "
            //           + "   where      FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'"
            //           + "   group by  datDepartment_1.DeptName"
            //           + "   order by [Rsum]  desc";
            //}
            //else 
            //{
            strsql += "select top 20 DeptName,SUM(Rsum) as Rsum from ( "
                     + "  select t.EmployeeCode,t.deptname +t.Cname  as DeptName,t.Rsum from  "
                     + "(select  dbo.fn_GetDepartComanyByUserID(a.AddUser) as deptname ,de.CName  as  Cname,de.EmployeeCode," 
                     + "isnull( CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2)),0) as [Rsum] "
                     + " from CrmPay a left outer join "
                     + " CrmProductPay  cpp  on cpp.PayId =a.ID  left outer join "
                     + " CrmOrderProduct  cop  on cop.ID =cpp.OrderProductId  left outer join" 
                     + " CrmCustomerOrder cco  on cco.ID =cop.OrderID left outer join" 
                     + " DatUser    du  on a.CZName =du.UserID  left  outer join" 
                     + " hrEmployee  de on du.EmpID =de.ID          "
                     + " where  a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "            
                     + " and cco.TypeID  not in ('5e50217a-414e-46d5-89bc-348ad0145ffc'," 
                     + " '53c8dae4-f3c9-4995-bb6a-560123574b81')"
                     + " and    FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'"
                     + " group by   a.AddUser ,de.CName,de.EmployeeCode   "    
                     + " ) t )z group by DeptName,EmployeeCode  order by Rsum  desc ";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有个人回款金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableAllPersonBackMoney(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
           // if (type == "0")
            //{
             //   strsql += " select top 20 datDepartment_1.DeptName+'/'+de.Name as  [DeptName],  CAST(round(cast(Sum(PayMoney) as decimal(9,2))/10000,3)  as  numeric(9,2))  as [Rsum]"
             //          +" from CrmPay a left outer join "
             //          +" CrmProductPay b on b.PayId =a.ID left outer join "
              //         +" DatDepartment d on a.DeptId = d.DeptID left outer join"
              //         +" DatUser    du  on a.AddUser =du.UserID  left  outer join "
              //         +" DatEmployee de on du.EmpID =de.EmpID  left outer  join"
              //         +" DatDepartment as datDepartment_1  on  datDepartment_1.DeptID=de.DeptID "
              //         + "  where      FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'"
              //         + " group by  datDepartment_1.DeptName+'/'+de.Name"
             //          +"  order by [Rsum]  desc";
            //}
            //else
            //{
            strsql += "select top 20 DeptName,SUM(Rsum) as Rsum from ( select  dbo.fn_GetDepartComanyByUserID(t.AddUser)+t.DeptName as DeptName ,t.Rsum from"  
                      +" (select a.AddUser,de.Name as  [DeptName],  CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [Rsum]"
                      +"  from CrmPay a left outer join "
                      +"  CrmProductPay b on b.PayId =a.ID left outer join "
                      +"   DatDepartment d on a.DeptId = d.DeptID left outer join"
                      +"  DatUser    du  on a.AddUser =du.UserID  left  outer join "
                      +"   DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                      +"   DatDepartment as datDepartment_1  on  datDepartment_1.DeptID=de.DeptID "
                      + "   where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and dbo.fn_GetPostByUserID(a.AddUser) like '%客户经理%' and a.AddUser in "
                      + "   (select   userid from  dbo.fn_GetOrganizeUser('" + DeptID + "')) "
                      + "   and  FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'"
                      +"   and de.Name is not null"
                      +"   group by  a.AddUser,de.Name"
                      + " ) t)z group by DeptName  order by Rsum  desc";

            //}
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有个人回款金额同比环比
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnComparisonAllPersonBackMoney(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type == "0")
            {
                strsql += " select top 20 datDepartment_1.DeptName+'-'+de.Name as  [DeptName],  CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [Sums],"
                       + " ("
                       + " select CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [Rsum] "
                       + " from CrmPay a1 left outer join "
                       + "    CrmProductPay b on b.PayId =a1.ID left outer join "
                       + "    DatDepartment d on a1.DeptId = d.DeptID left outer join"
                       + "    DatUser    du  on a1.AddUser =du.UserID  left  outer join "
                       + "    DatEmployee de1 on du.EmpID =de1.EmpID  left outer  join"
                       + "    DatDepartment as datDepartment_2  on  datDepartment_2.DeptID=de1.DeptID "
                       + " where  a1.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  datDepartment_1.DeptName+'-'+de.Name= datDepartment_2.DeptName+'/'+de1.Name and  FactPayDate  between  '" + StartDate.AddMonths(-1) + "'  and '" + EndDate.AddMonths(-1) + "' AND  de1.Name  is not null "
                       + "  ) as  [PMSum],"
                       + "    ("
                       + " select CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [Rsum] "
                       + " from CrmPay a2 left outer join "
                       + "    CrmProductPay b on b.PayId =a2.ID left outer join "
                       + "    DatDepartment d on a2.DeptId = d.DeptID left outer join"
                       + "    DatUser    du  on a2.AddUser =du.UserID  left  outer join "
                       + "    DatEmployee de2 on du.EmpID =de2.EmpID  left outer  join"
                       + "    DatDepartment as datDepartment_3  on  datDepartment_3.DeptID=de2.DeptID"
                       + "   where  a2.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and datDepartment_1.DeptName+'-'+de.Name= datDepartment_3.DeptName+'/'+de2.Name and  FactPayDate  between  '" + StartDate.AddYears(-1) + "'  and '" + EndDate.AddYears(-1) + "' AND  de2.Name  is not null "
                       + "  ) as  [PYSum]"
                       + " from CrmPay a left outer join "
                       + "  CrmProductPay b on b.PayId =a.ID left outer join"
                       + "  DatDepartment d on a.DeptId = d.DeptID left outer join"
                       + "  DatUser    du  on a.AddUser =du.UserID  left  outer join "
                       + " DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                       + " DatDepartment as datDepartment_1  on  datDepartment_1.DeptID=de.DeptID "
                       + " where    a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'  AND  de.Name  is not null"
                       + " group by  datDepartment_1.DeptName+'-'+de.Name"
                       + " order by [Sums]  desc";          
            }
            else
            {
                strsql += " select top 20 datDepartment_1.DeptName+'-'+de.Name as  [DeptName],  CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [Sums],"
                       + " ("
                       + " select CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [Rsum] "
                       + " from CrmPay a1 left outer join "
                       + "    CrmProductPay b on b.PayId =a1.ID left outer join "
                       + "    DatDepartment d on a1.DeptId = d.DeptID left outer join"
                       + "    DatUser    du  on a1.AddUser =du.UserID  left  outer join "
                       + "    DatEmployee de1 on du.EmpID =de1.EmpID  left outer  join"
                       + "    DatDepartment as datDepartment_2  on  datDepartment_2.DeptID=de1.DeptID "
                       + " where a1.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and datDepartment_1.DeptName+'-'+de.Name= datDepartment_2.DeptName+'/'+de1.Name and  FactPayDate  between  '" + StartDate.AddMonths(-1) + "'  and '" + EndDate.AddMonths(-1) + "' AND  de1.Name  is not null"
                       + "  ) as  [PMSum],"
                       + "    ("
                       + " select CAST(round(cast(Sum(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [Rsum] "
                       + " from CrmPay a2 left outer join "
                       + "    CrmProductPay b on b.PayId =a2.ID left outer join "
                       + "    DatDepartment d on a2.DeptId = d.DeptID left outer join"
                       + "    DatUser    du  on a2.AddUser =du.UserID  left  outer join "
                       + "    DatEmployee de2 on du.EmpID =de2.EmpID  left outer  join"
                       + "    DatDepartment as datDepartment_3  on  datDepartment_3.DeptID=de2.DeptID"
                       + "   where a2.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and datDepartment_1.DeptName+'-'+de.Name= datDepartment_3.DeptName+'/'+de2.Name and  FactPayDate  between  '" + StartDate.AddYears(-1) + "'  and '" + EndDate.AddYears(-1) + "' AND  de2.Name  is not null "
                       + "  ) as  [PYSum]"
                       + " from CrmPay a left outer join "
                       + "  CrmProductPay b on b.PayId =a.ID left outer join"
                       + "  DatDepartment d on a.DeptId = d.DeptID left outer join"
                       + "  DatUser    du  on a.AddUser =du.UserID  left  outer join "
                       + " DatEmployee de on du.EmpID =de.EmpID  left outer  join"
                       + " DatDepartment as datDepartment_1  on  datDepartment_1.DeptID=de.DeptID "
                       + " where a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' and  a.AddUser in (select   userid from  dbo.fn_GetOrganizeUser('" + DeptID + "')) and    FactPayDate  between  '" + StartDate + "'  and '" + EndDate + "'  AND  de.Name  is not null"
                       + " group by  datDepartment_1.DeptName+'-'+de.Name"
                       + " order by [Sums]  desc"; 
            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有分公司销售金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableSaleMoneyDept(string DeptID, string type, int iMonth, int iYear, int iNMonth, int iNYear, string tempFw)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            string strcon = "";
            if (tempFw == "yes")
            {
                if (iNMonth == 12)
                {
                    iNYear = iNYear + 1;
                    iNMonth = 0;
                }
                strcon = "Date>='" + iYear + "-" + iMonth + "-1' and Date<='" + iNYear + "-" + (iNMonth + 1) + "-1'";
            }
            else
            {
                strcon ="DATEPART(YEAR,Date)=" + iYear + " AND DATEPART(MONTH,Date)=" + iMonth;
            }
            if (DeptID == "")
            {
                strsql = "select  LEFT(deptName,2) as deptName,SUM(TotalMoney) as backPay,min(Date) from V_CrmCustomerInfoOrders where " + strcon + "  group by LEFT(deptName,2) order by backPay desc";
            }
            else
            {
                if (type == "1")  //个人
                {
                    strsql = "select distinct CustomerMgr as deptName , SUM(TotalMoney) as backPay,min(Date) from V_CrmCustomerInfoOrders where CustomeerMgr='" + DeptID + "' and " + strcon + " group by CustomerMgr";
                }
                else if (type == "2")//团队   a2ac801f-bc66-4911-b021-56288c51e04a'))
                {
                    strsql = "select distinct CustomerMgr as deptName , SUM(TotalMoney) as backPay,min(Date) from V_CrmCustomerInfoOrders where CustomeerMgr in(select userid from datuser where empid in (select ID from hrEmployee where DepartmentID='" + DeptID + "')) and " + strcon + " group by CustomerMgr";
                }
                else
                {
                    strsql = "select dd.DeptName as deptName ,SUM(TotalMoney) as backPay  from DatDepartment dd INNER JOIN DatDepartment dd1 ON dd.ParentID=dd1.DeptID INNER JOIN hrEmployee e ON dd.DeptID=e.DepartmentID AND dd1.ParentID='" + DeptID + "' INNER JOIN DatUser du ON du.EmpID=e.ID INNER JOIN V_CrmCustomerInfoOrders co ON du.UserID=co.CustomeerMgr where DATEPART(YEAR,Date)=" + iYear + " AND DATEPART(MONTH,co.Date)=" + iMonth + " group by dd.DeptName";
                }
                //  strsql = "select DeptId,LEFT(deptName,2) as deptName,SUM(paymoney) as backPay from v_CrmProductPay where deptid='" + DeptID + "' and PayId in(select ID from CrmPay where DATEPART(MONTH,FactPayDate)=" + iMonth + " ) group by DeptId,deptname order by backPay desc";
            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有分公司回款率
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTablePercentBackMoneyDept(string DeptID, string type, int iMonth, int iYear, int iNMonth, int iNYear,string tempFw)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            string strcon = "";
            if (tempFw == "yes")
            {
                if (iNMonth == 12)
                {
                    iNYear = iNYear + 1;
                    iNMonth = 0;
                }
                strcon = " Date>='" + iYear + "-" + iMonth + "-1' and Date<='" + iNYear + "-" + (iNMonth + 1) + "-1'";
            }
            else
            {
                strcon = " DATEPART(YEAR,Date)=" + iYear + " AND DATEPART(MONTH,Date)=" + iMonth;
            }
            if (DeptID == "")
            {
                strsql = " select top 25 LEFT(deptName,2) as deptName,(SUM(OrderResignMoney)*100/sum(TotalMoney)) as backPay,min(Date) from V_CrmCustomerInfoOrders where " + strcon + "  group by LEFT(deptName,2) having sum(TotalMoney)>0 order by backPay desc";
            }
            else
            {
                if (type == "1")
                {
                    strsql = " select distinct CustomerMgr as deptName ,(SUM(OrderResignMoney)*100/sum(TotalMoney)) as backPay,min(Date) from V_CrmCustomerInfoOrders where CustomeerMgr='" + DeptID + "' and " + strcon + " group by CustomerMgr having sum(TotalMoney)>0 ";
                }
                else if (type == "2")//团队   a2ac801f-bc66-4911-b021-56288c51e04a'))
                {
                    strsql = " select distinct CustomerMgr as deptName , (SUM(OrderResignMoney)*100/sum(TotalMoney)) as backPay,min(Date) from V_CrmCustomerInfoOrders where CustomeerMgr in(select userid from datuser where empid in (select ID from hrEmployee where DepartmentID='" + DeptID + "')) and " + strcon + " group by CustomerMgr having sum(TotalMoney)>0 ";
                }
                else
                {
                    if (tempFw == "yes")
                    {
                        if (iNMonth == 12)
                        {
                            iNYear = iNYear + 1;
                            iNMonth = 0;
                        }
                        strcon = " co.Date>='" + iYear + "-" + iMonth + "-1' and co.Date<='" + iNYear + "-" + (iNMonth + 1) + "-1'";
                    }
                    else
                    {
                        strcon = " DATEPART(YEAR,Date)=" + iYear + " AND DATEPART(MONTH,co.Date)=" + iMonth;
                    }
                    strsql = " select dd.DeptName as deptName ,(SUM(OrderResignMoney)*100/sum(TotalMoney)) as backPay,min(Date)  from DatDepartment dd INNER JOIN DatDepartment dd1 ON dd.ParentID=dd1.DeptID INNER JOIN hrEmployee e ON dd.DeptID=e.DepartmentID AND dd1.ParentID='" + DeptID + "' INNER JOIN DatUser du ON du.EmpID=e.ID INNER JOIN V_CrmCustomerInfoOrders co ON du.UserID=co.CustomeerMgr where " + strcon + " group by dd.DeptName having sum(TotalMoney)>0 ";
                }
            } DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有分公司价值业绩销售金额
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableValueDept(string DeptID, string type, int iMonth, int iYear, int iNMonth, int iNYear, string tempFw)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            string strcon = "";
            if (tempFw == "yes")
            {
                if (iNMonth == 12)
                {
                    iNYear = iNYear + 1;
                    iNMonth = 0;
                }
                strcon = "APDate>='" + iYear + "-" + iMonth + "-1' and APDate<='" + iNYear + "-" + (iNMonth + 1) + "-1'";
            }
            else
            {
                strcon = "DATEPART(YEAR,APDate)=" + iYear + " AND DATEPART(MONTH,APDate)=" + iMonth;
            }
            if (DeptID == "")
            {
                strsql = "select  LEFT(deptName,2) as DeptName,SUM(PayMoney) as PayMoney,min(APDate) from V_CrmValueTotal where " + strcon + "  group by LEFT(deptName,2) order by PayMoney desc";
            }
            else
            {
                if (type == "1")  //个人
                {
                    strsql = "select distinct CName as deptName , SUM(PayMoney) as PayMoney,min(APDate) from V_CrmValueTotal where CustomeerMgr='" + DeptID + "' and " + strcon + " group by CName";
                }
                else if (type == "2")//团队   a2ac801f-bc66-4911-b021-56288c51e04a'))
                {
                    strsql = "select distinct CName as DeptName , SUM(PayMoney) as PayMoney,min(APDate) from V_CrmValueTotal where CustomeerMgr in(select userid from datuser where empid in (select ID from hrEmployee where DepartmentID='" + DeptID + "')) and " + strcon + " group by CName";
                }
                else
                {
                    strsql = "select dd.DeptName as DeptName ,SUM(PayMoney) as PayMoney from DatDepartment dd INNER JOIN DatDepartment dd1 ON dd.ParentID=dd1.DeptID INNER JOIN hrEmployee e ON dd.DeptID=e.DepartmentID AND dd1.ParentID='" + DeptID + "' INNER JOIN DatUser du ON du.EmpID=e.ID INNER JOIN V_CrmValueTotal co ON du.UserID=co.CustomeerMgr where DATEPART(YEAR,APDate)=" + iYear + " AND DATEPART(MONTH,co.APDate)=" + iMonth + " group by dd.DeptName";
                }
            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有分公司价值业绩占有率
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTablePercentValueDept(string DeptID, string type, int iMonth, int iYear, int iNMonth, int iNYear, string tempFw)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            string strcon = "";
            if (tempFw == "yes")
            {
                if (iNMonth == 12)
                {
                    iNYear = iNYear + 1;
                    iNMonth = 0;
                }
                strcon = "APDate>='" + iYear + "-" + iMonth + "-1' and APDate<='" + iNYear + "-" + (iNMonth + 1) + "-1'";
            }
            else
            {
                strcon = "DATEPART(YEAR,APDate)=" + iYear + " AND DATEPART(MONTH,APDate)=" + iMonth;
            }
            if (DeptID == "")
            {
                strsql = "select top 25 LEFT(deptName,2) as DeptName,(SUM(PayMoney)/sum(TotalMoney)) as PayMoney,min(APDate) from V_CrmValueTotal where " + strcon + "  group by LEFT(deptName,2) having sum(PayMoney)>0 order by PayMoney desc";
            }
            else
            {
                if (type == "1")                                        
                {
                    strsql = "select distinct CName as DeptName ,(SUM(PayMoney)/sum(TotalMoney)) as PayMoney,min(APDate) from V_CrmValueTotal where CustomeerMgr='" + DeptID + "' and " + strcon + " group by CName having sum(TotalMoney)>0 ";
                }
                else if (type == "2")//团队   a2ac801f-bc66-4911-b021-56288c51e04a'))
                {
                    strsql = "select distinct CName as DeptName , (SUM(PayMoney)/sum(TotalMoney)) as PayMoney,min(APDate) from V_CrmValuePay where CustomeerMgr in(select userid from datuser where empid in (select ID from hrEmployee where DepartmentID='" + DeptID + "')) and " + strcon + " group by CName having sum(TotalMoney)>0 ";
                }
                else
                {
                    if (tempFw == "yes")
                    {
                        if (iNMonth == 12)
                        {
                            iNYear = iNYear + 1;
                            iNMonth = 0;
                        }
                        strcon = "co.APDate>='" + iYear + "-" + iMonth + "-1' and co.APDate<='" + iNYear + "-" + (iNMonth + 1) + "-1'";
                    }
                    else
                    {
                        strcon = "DATEPART(YEAR,APDate)=" + iYear + " AND DATEPART(MONTH,co.APDate)=" + iMonth;
                    }
                    strsql = "select dd.DeptName as DeptName ,(SUM(PayMoney)/sum(TotalMoney)) as PayMoney,min(APDate)  from DatDepartment dd INNER JOIN DatDepartment dd1 ON dd.ParentID=dd1.DeptID INNER JOIN hrEmployee e ON dd.DeptID=e.DepartmentID AND dd1.ParentID='" + DeptID + "' INNER JOIN DatUser du ON du.EmpID=e.ID INNER JOIN V_CrmValueTotal co ON du.UserID=co.CustomeerMgr where " + strcon + " group by dd.DeptName having sum(TotalMoney)>0 ";
                }
            } DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有客户信息--客户行业
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableTradeName(Guid UserID)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select top 30  TradeName, COUNT(ID) as  typesum  from dbo.V_CrmCustomerInfo where CustomUser in('" + UserID.ToString() + "') group by  TradeName  order  by typesum desc, TradeName  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有客户信息--客户行业
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableTradeName(Guid deptid,DateTime startdate,DateTime enddate,string  type)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type != "3")
            {
                strsql = "select  top 20 cus.TradeName  as name,"
                            + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                            + "  CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                            + "  V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                            + " where    cp.AddUser in (select   userid from  dbo.fn_GetOrganizeUser('" + deptid + "')) and FactPayDate  between  '" + startdate + "'  and  '" + enddate + "' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D'"
                            + " group by  cus.TradeName"
                            + " order by [sum] desc";

            }
            else
            {
                strsql = "select  top 20 cus.TradeName  as name,"
                            + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                            + "  CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                            + "  V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                            + " where    cp.AddUser in ('" + deptid + "') and FactPayDate  between  '" + startdate + "'  and  '" + enddate + "' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D'"
                            + " group by  cus.TradeName"
                            + " order by [sum] desc";
            }
             
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }

        /// <summary>
        /// 获取所有客户信息--销售渠道
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableChannelName(Guid deptid, DateTime startdate, DateTime enddate,string  type)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type != "3")
            {
                strsql = "select PhraseText as  name,"
                         + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]"
                         + " from CrmProductPay   c  left  outer join "
                         + " CrmPay   cc  on cc.ID =c.PayId  left outer join"
                         + " CrmOrderProduct  CP  ON  c.OrderProductId =CP.ID   left outer join "
                         + " CrmCustomerOrder co on  CP.OrderID =co.ID  left outer join"
                         + " DatPhraseDetail  dp on co.TypeID=dp.PhraseID"
                         + " where cc.AddUser in (select   userid from  dbo.fn_GetOrganizeUser('" + deptid + "')) and  cc.FactPayDate  between  '"+startdate+"'  and  '"+enddate+"' and  cc.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D'"
                         + " group by  PhraseText "
                         + " order by [sum]desc";
            }
            else
            {
                strsql = "select PhraseText as  name,"
                          + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]"
                          + " from CrmProductPay   c  left  outer join "
                          + " CrmPay   cc  on cc.ID =c.PayId  left outer join"
                          + " CrmOrderProduct  CP  ON  c.OrderProductId =CP.ID   left outer join "
                          + " CrmCustomerOrder co on  CP.OrderID =co.ID  left outer join"
                          + " DatPhraseDetail  dp on co.TypeID=dp.PhraseID"
                          + " where cc.AddUser in ('" + deptid + "') and  cc.FactPayDate  between  '"+startdate+"'  and  '"+enddate+"' and  cc.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D'"
                          + " group by  PhraseText "
                          + " order by [sum] desc";
            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }

        /// <summary>
        /// 获取所有客户信息--客户产值
        /// </summary>         
        /// <returns></returns>
        public DataTable ReturnTableProductNameDeptID(Guid deptid)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select  ProductName, COUNT(ID) as  typesum  from dbo.V_CrmCustomerInfo where CustomUser in(select  userid  from fn_GetOrganizeUser('" + deptid.ToString() + "')) group by  ProductName  order  by ProductName  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }

        /// <summary>
        /// 获取所有客户信息--客户产值
        /// </summary>         
        /// <returns></returns>
        public DataTable ReturnTableProductName(Guid deptid, DateTime startdate, DateTime enddate,string type)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type != "3")
            {
                 strsql = " select cus.ProductName  as name,"
                                + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                                + " CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                                + " V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                                + " where cp.AddUser in (select   userid from  dbo.fn_GetOrganizeUser('" + deptid + "')) and FactPayDate  between  '" + startdate + "'  and  '" + enddate + "' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D'"
                                + " group by  cus.ProductName"
                                + " order by [sum]desc";
            }
            else
            {
                strsql = " select cus.ProductName  as name,"
                                + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                                + " CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                                + " V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                                + " where cp.AddUser in ('" + deptid + "') and FactPayDate  between  '" + startdate + "'  and  '" + enddate + "' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D'"
                                + " group by  cus.ProductName"
                                + " order by [sum] desc";
 
            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }

        /// <summary>
        /// 获取所有客户信息--客户规模
        /// </summary>         
        /// <returns></returns>
        public DataTable ReturnTablePersonName(Guid deptid, DateTime startdate, DateTime enddate,string  type)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type != "3")
            {
                strsql = "  select cus.PersonName  as name,"
                               + "  CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                               + "  CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                               + "  V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                               + "  where cp.AddUser in (select   userid from  dbo.fn_GetOrganizeUser('" + deptid + "'))  and  FactPayDate  between  '"+startdate+"'  and  '"+enddate+"' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                               + "  group by  cus.PersonName  "
                               + " order by [sum] desc";
            }
            else
            {
                strsql = "  select cus.PersonName  as name,"
                              + "  CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                              + "  CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                              + "  V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                              + "  where cp.AddUser in ('" + deptid + "') and  FactPayDate  between  '"+startdate+"'  and  '"+enddate+"' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                              + "  group by  cus.PersonName  "
                              + " order by [sum] desc";
            }

            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }



        /// <summary>
        /// 获取所有客户信息--城市
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableCityName(Guid deptid, DateTime startdate, DateTime enddate,string  type)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type != "3")
            {
                strsql = "select top 10 cus.City   as name,"
                               + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                               + " CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                               + " V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                               + " where cp.AddUser in (select   userid from  dbo.fn_GetOrganizeUser('" + deptid + "'))  and  FactPayDate  between  '"+startdate+"'  and  '"+enddate+"' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                               + " group by  cus.City "
                               + " order by [sum] desc";
            }
            else
            {
                strsql = "select  top 10 cus.City   as name,"
                               + " CAST(round(cast(SUM(PayMoney) as decimal(18,9))/10000,3)  as  numeric(9,2))   as [sum]   from  CrmProductPay  cpp  left outer  join "
                               + " CrmPay   cp  on  cpp.PayId =cp.ID  left outer join"
                               + " V_CrmCustomerInfo cus  on cp.CustomerId =cus.ID "
                               + " where cp.AddUser in ('" + deptid + "')  and  FactPayDate  between  '"+startdate+"'  and  '"+enddate+"' and  cp.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                               + " group by  cus.City "
                               + " order by [sum] desc";
            }


            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有客户信息--客户产值
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnPersonName(Guid UserID)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select  PersonName, COUNT(ID) as  typesum  from dbo.V_CrmCustomerInfo where CustomUser in('" + UserID.ToString() + "') group by  PersonName  order  by PersonName  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有客户信息--客户规模
        /// </summary>         
        /// <returns></returns>
        public DataTable ReturnTablePersonNameDeptID(Guid deptid)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select  PersonName,  COUNT(ID) as  typesum  from dbo.V_CrmCustomerInfo where CustomUser in(select  userid  from fn_GetOrganizeUser('" + deptid.ToString() + "')) group by  PersonName  order  by PersonName  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有客户信息--客户规模
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnProductName(Guid UserID)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select  ProductName,  COUNT(ID) as  typesum  from dbo.V_CrmCustomerInfo where CustomUser in('" + UserID.ToString() + "') group by  ProductName  order  by ProductName  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 根据当前登陆用户编号获取客户编号
        /// </summary>
        public DataTable ReturnProductCustomerByUserID(Guid UserID)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select  CustomUser  from dbo.V_CrmCustomerInfo where CustomUser in('" + UserID.ToString() + "') ";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 根据当前登陆用户所在部门编号获取客户编号
        /// </summary>
        public DataTable ReturnProductCustomerByDeptID(Guid deptid)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "select CustomUser where CustomUser in(select  userid  from fn_GetOrganizeUser('" + deptid.ToString() + "')";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 产品分统计类报表
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="ForDataType">0：本月数据、1：本年数据</param>
        /// <param name="ForDataType">0：所有人数据[配置为0或者不配置时]、1：根据权限过滤数据</param>
        /// <returns></returns>
        public DataTable ReturnProductTypeReprot(Guid UserID, int ForDataType, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@ForDataType",SqlDbType.Int,1),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };

            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = ForDataType;
            sqlparams[2].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnProductTypeReport", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }

        /// <summary>
        /// 渠道分统计类报表
        /// </summary>
        /// <param name="CustomIDS"></param>
        /// <param name="ForDataType">0：本月数据、1：本年数据</param>
        /// <param name="ForDataType">0：所有人数据[配置为0或者不配置时]、1：根据权限过滤数据</param>
        /// <returns></returns>
        public DataTable ReturnChanelTypeReprot(Guid UserID, int ForDataType, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@ForDataType",SqlDbType.Int,1),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };

            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = ForDataType;
            sqlparams[2].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnChanelTypeReport", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }


        /// <summary>
        /// 渠道分统计企业内训报表
        /// </summary>
        /// <param name="CustomIDS"></param>
        /// <param name="ForDataType">0：本月数据、1：本年数据</param>
        /// <param name="ForDataType">0：所有人数据[配置为0或者不配置时]、1：根据权限过滤数据</param>
        /// <returns></returns>
        public DataTable ReturnTamingReport(Guid UserID, int ForDataType, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@ForDataType",SqlDbType.Int,1),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };

            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = ForDataType;
            sqlparams[2].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnTamingReport", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        /// <summary>
        /// 企业内训	已交付	未交付	交付中
        /// </summary>
        public DataTable ReturnTamingReport(Guid UserID, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };

            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnTamingCrmOrderProductReport", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }

        /// <summary>
        /// 信息统计报表
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="ForDataType">0：本月数据、1：本年数据</param>
        /// <param name="ForDataType">0：所有人数据[配置为0或者不配置时]、1：根据权限过滤数据</param>
        /// <returns></returns>
        public DataTable ReturnMessageReport(Guid UserID, int ForDataType, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@ForDataType",SqlDbType.Int,1),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };

            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = ForDataType;
            sqlparams[2].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnMessageReport", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }

        /// <summary>
        /// 销售、回款报表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable ReturnSPCountReport(Guid UserID, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };
            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnSPCountReport", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }

        /// <summary>
        /// 销售、回款报表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable ReturnProjectCountReport(Guid UserID, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };
            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = PowerType;
            DataSet ds = execsql.RunProcedure("[sp_ReturnProjectCountReport]", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        /// <summary>
        /// 销售渠道报表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable ReturnSaleRoad(Guid UserID, string sDate, string eDate, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@sDate",SqlDbType.VarChar,50),
                new SqlParameter("@eDate",SqlDbType.VarChar,50),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };
            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = sDate;
            sqlparams[2].Value = eDate;
            sqlparams[3].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnSaleRoad", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        /// <summary>
        /// 价值业绩回款报表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable ReturnValuePercent(Guid UserID, string sDate, string eDate, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@sDate",SqlDbType.VarChar,50),
                new SqlParameter("@eDate",SqlDbType.VarChar,50),
                new SqlParameter("@PowerType",SqlDbType.Int,1),
                new SqlParameter("@showType",SqlDbType.Int,1)
             };
            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = sDate;
            sqlparams[2].Value = eDate;
            sqlparams[3].Value = PowerType;
            sqlparams[4].Value = 2;
            DataSet ds = execsql.RunProcedure("sp_ReturnValuePercent", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        /// <summary>
        /// 产品、已交付、未交付、交付中统计报表
        /// </summary>
        public DataTable ReturnCrmOrderProductReport(Guid UserID, int ProductType, int PowerType)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@ProductType",SqlDbType.Int,1),
                new SqlParameter("@PowerType",SqlDbType.Int,1)
             };
            sqlparams[0].Value = UserID.ToString();
            sqlparams[1].Value = ProductType;
            sqlparams[2].Value = PowerType;
            DataSet ds = execsql.RunProcedure("sp_ReturnCrmOrderProductReport", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }

        /// <summary>
        /// 个人排名报表
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="deptName"></param>
        /// <returns></returns>
        public DataTable ReturnAllPersonBackMoneyRankReport(string startDate, string endDate, string deptName)
        {
            DataTable dt = new DataTable();
            DbSql db = new DbSql();
            string strSql = string.Format("select * from fn_AllPersonBackMoneyRANK('{0}','{1}')", startDate, endDate);
            if (deptName != "")
            {
                strSql += " where DeptName like '%" + deptName + "%'";
            }
            dt = db.ExecuteQuery(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt;
            }
            else
            {
                return new DataTable();
            }
        }
        /// <summary>
        /// 业绩排名报表
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="deptName"></param>
        /// <returns></returns>
        public DataTable ReturnAllTeamBackMoneyRankReport(string startDate, string endDate, string deptName)
        {
            DataTable dt = new DataTable();
            DbSql db = new DbSql();
            string strSql = string.Format("select * from fn_AllTeamBackMoneyRANK('{0}','{1}')", startDate, endDate);
            if (deptName != "")
            {
                strSql += " where DeptName like '%" + deptName + "%' or DeptManagerName like '" + deptName + "'";
            }
            dt = db.ExecuteQuery(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt;
            }
            else
            {
                return new DataTable();
            }
        }
        /// <summary>
        /// 回款转盘统计
        /// </summary>
        /// <param name="strUserId">用户ID</param>
        /// <param name="strPowerType">是否控制权限  0:否 1:是</param>
        /// <param name="strLoadType">加载显示类型  0:本月 1:本季度 2:本年</param>
        /// <returns></returns>
        public DataTable ReturnCircleBackMondyReport(string strUserId, int strPowerType, int strLoadType)
        {
            DataTable dt = new DataTable();
            DbSql db = new DbSql();
            try
            {
                string strSql = string.Format("select * from fn_GetCircularGaugeReport('{0}','{1}','{2}')", strUserId, strPowerType, strLoadType);
                dt = db.ExecuteQuery(strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        /// <summary>
        /// 课程交付报表统计
        /// </summary>
        /// <param name="strPowerType"></param>
        /// <param name="strLoadType"></param>
        /// <returns></returns>
        public DataTable ReturnCourseDeliveryReport(string strUserId, int strPowerType, int strLoadType)
        {
            DataTable dt = new DataTable();
            DbSql db = new DbSql();
            try
            {
                string strSql = string.Format("select * from fn_GetCourseDeliveryReport('{0}','{1}','{2}')", strUserId, strPowerType, strLoadType);
                dt = db.ExecuteQuery(strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        /// <summary>
        /// 按月统计报表
        /// </summary>
        /// <param name="deptid">if=0,1,2 是部门ID if=3 用户ID</param>
        /// <param name="startdate">开始日期</param>
        /// <param name="endate">结束日期</param>
        /// <param name="type">0:全部 1:分公司  2：团队  3:个人</param>
        /// <returns></returns>
        public DataTable ReturnByBackMoneyMonth(string deptid, DateTime startdate, DateTime  endate,string type)
        {
            DataTable dt = new DataTable();
            string strSql = string.Empty;
            DbSql db = new DbSql();
            try
            {
                if (type == "0")
                {
                     strSql = string.Format("select   name, Rsum  from ("
                             + "select "
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12]"
                             + "  from CrmPay a left outer join "
                             + "  CrmProductPay b on b.PayId =a.ID "
                             + "  where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                             + "  and   FactPayDate  between  '{0}' and  '{1}') a "
                             + "  unpivot"
                             + "  ("
                             + "  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])"
                             + "  ) as  ff", startdate, endate);
                    
                }
                else if (type =="1")
                {
                     strSql = string.Format("select   name, Rsum  from ("
                            + "select "
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12]"
                            + "  from CrmPay a left outer join "
                            + "  CrmProductPay b on b.PayId =a.ID "
                            + "  where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                            + "  and   FactPayDate  between  '{0}' and  '{1}' and DeptId ='{2}') a "
                            + "  unpivot"
                            + "  ("
                            + "  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])"
                            + "  ) as  ff", startdate, endate,deptid);
              }
              else if (type == "2")
               {
                    strSql = string.Format("select   name, Rsum  from ("
                            + "select "
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                            + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12]"
                            + "  from CrmPay a left outer join "
                            + "  CrmProductPay b on b.PayId =a.ID "
                            + "  where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                            + "  and   FactPayDate  between  '{0}' and  '{1}' and a.AddUser ='{2}') a "
                            + "  unpivot"
                            + "  ("
                            + "  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])"
                            + "  ) as  ff", startdate, endate, deptid);
 
                }
                else if (type == "3")
                {
                     strSql = string.Format("select   name, Rsum  from ("
                             + "select "
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                             + "  CAST(round(cast(SUM(case  when(DATEPART(mm,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12]"
                             + "  from CrmPay a left outer join "
                             + "  CrmProductPay b on b.PayId =a.ID "
                             + "  where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                             + "  and   FactPayDate  between  '{0}' and  '{1}' and a.AddUser ='{2}') a "
                             + "  unpivot"
                             + "  ("
                             + "  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])"
                             + "  ) as  ff", startdate, endate, deptid);

                }
                dt = db.ExecuteQuery(strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 按日统计报表
        /// </summary>
        /// <param name="deptid">if=0,1,2 是部门ID if=3 用户ID</param>
        /// <param name="startdate">开始日期</param>
        /// <param name="endate">结束日期</param>
        /// <param name="type">0:全部 1:分公司  2：团队  3:个人</param>
        /// <returns></returns>
        public DataTable ReturnByBackMoneyDay(string deptid, DateTime startdate, DateTime endate, string type)
        {
            DataTable dt = new DataTable();
            string strSql = string.Empty;
            DbSql db = new DbSql();
            try
            {
                if (type == "0")
                {
                     strSql = string.Format("select   name, Rsum  from ("
                          +" select "
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=13) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [13],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=14) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [14],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=15) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [15],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=16) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [16],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=17) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [17],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=18) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [18],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=19) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [19],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=20) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [20],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=21) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [21],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=22) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [22],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=23) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [23],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=24) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [24],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=25) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [25],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=26) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [26],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=27) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [27],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=28) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [28],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=29) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [29],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=30) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [30],"
                          +" CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=31) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [31]"
                          +" from CrmPay a left outer join "
                          +" CrmProductPay b on b.PayId =a.ID "
                          +" where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                          +" and   FactPayDate  between  '{0}' and  '{1}' ) a "
                          +" unpivot"
                          +" ("
                          +"  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])"
                          +" ) as  ff", startdate, endate);
                }
                else if (type == "1")
                {
                    strSql = string.Format("select   name, Rsum  from ("
                         + " select "
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=13) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [13],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=14) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [14],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=15) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [15],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=16) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [16],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=17) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [17],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=18) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [18],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=19) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [19],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=20) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [20],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=21) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [21],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=22) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [22],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=23) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [23],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=24) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [24],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=25) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [25],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=26) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [26],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=27) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [27],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=28) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [28],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=29) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [29],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=30) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [30],"
                         + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=31) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [31]"
                        + " from CrmPay a left outer join "
                        + " CrmProductPay b on b.PayId =a.ID "
                        + " where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                        + " and   FactPayDate  between  '{0}' and  '{1}' and deptid ='{2}' ) a"
                        + " unpivot"
                        + " ("
                        + "  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])"
                        + " ) as  ff", startdate, endate,deptid);
                }
                else if (type == "2")
                {
                    strSql = string.Format("select   name, Rsum  from ("
                        + " select "
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=13) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [13],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=14) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [14],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=15) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [15],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=16) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [16],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=17) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [17],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=18) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [18],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=19) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [19],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=20) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [20],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=21) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [21],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=22) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [22],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=23) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [23],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=24) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [24],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=25) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [25],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=26) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [26],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=27) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [27],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=28) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [28],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=29) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [29],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=30) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [30],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=31) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [31]"
                       + " from CrmPay a left outer join "
                       + " CrmProductPay b on b.PayId =a.ID "
                       + " where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                       + " and   FactPayDate  between  '{0}' and  '{1}' ) a"
                       + " unpivot"
                       + " ("
                       + "  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])"
                       + " ) as  ff", startdate, endate);

                }
                else if (type == "3")
                {
                    strSql = string.Format("select   name, Rsum  from ("
                        + " select "
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=1) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [1],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=2) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [2],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=3) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [3],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=4) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [4],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=5) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [5],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=6) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [6],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=7) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [7],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=8) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [8],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=9) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [9],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=10) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [10],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=11) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [11],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=12) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [12],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=13) then PayMoney  else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2)) as [13],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=14) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [14],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=15) then PayMoney else  0 end) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [15],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=16) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [16],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=17) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [17],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=18) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [18],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=19) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [19],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=20) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [20],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=21) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [21],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=22) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [22],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=23) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [23],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=24) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [24],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=25) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [25],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=26) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [26],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=27) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [27],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=28) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [28],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=29) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [29],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=30) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [30],"
                        + " CAST(round(cast(SUM(case  when(DATEPART(DD,FactPayDate)=31) then PayMoney else  0 end)as decimal(18,9))/10000,3)  as  numeric(9,2)) as [31]"
                       + " from CrmPay a left outer join "
                       + " CrmProductPay b on b.PayId =a.ID "
                       + " where   a.State ='86B67A2C-8DF8-417C-B70D-6D54969B812D' "
                       + " and   FactPayDate  between  '{0}' and  '{1}'  and a.AddUser ='{2}' ) a"
                       + " unpivot"
                       + " ("
                       + "  Rsum for name in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])"
                       + " ) as  ff", startdate, endate,deptid);

                }
                dt = db.ExecuteQuery(strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        /// <summary>
        /// 获取所有分公司价值业绩
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableAllValueDept(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            if (type == "0")
            {
                strsql += @"  select top 25 DeptName,SUM(PayMoney) as PayMoney from (  select cv.DeptName, CAST(round(cast(Sum(cv.PayMoneyTotal) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [PayMoney] from V_CrmValueRpt cv
                          where cv.DliverState ='20F825C4-292A-4386-8157-923668BD2682' and   cv.AddUser in 
                          (select   userid from  dbo.fn_GetOrganizeUser('FB282647-FFE0-440B-81B2-1466D3EAC417')) 
                          and  cv.APDate  between '" + StartDate + "' and '" + EndDate + @"'
                          and cv.DeptName is not null
                          group by  cv.DeptName
                          )a group by DeptName  order by [PayMoney]  desc ";
            }
            else
            {
                strsql += @" select top 25 DeptName,SUM(PayMoney) as PayMoney from ( select cv.DeptName, CAST(round(cast(Sum(cv.PayMoneyTotal) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [PayMoney]
                          from V_CrmValueRpt cv
                          where cv.DliverState ='20F825C4-292A-4386-8157-923668BD2682' and   cv.AddUser in 
                          (select   userid from  dbo.fn_GetOrganizeUser('FB282647-FFE0-440B-81B2-1466D3EAC417')) 
                          and  cv.APDate  between '" + StartDate + "' and '" + EndDate + @"'
                          and cv.DeptName is not null
                          group by  cv.DeptName
                          )a group by DeptName  order by [PayMoney]  desc ";

            }
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有客户总监价值业绩
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableAllTeamValue(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            strsql += @"select top 20 ZjName,SUM(PayMoney) as PayMoney from ( select cv.DeptName+cv.CZName as ZjName , CAST(round(cast(Sum(cv.PayMoneyTotal) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [PayMoney] from V_CrmValueRpt cv
                      where cv.DliverState ='20F825C4-292A-4386-8157-923668BD2682'  and   cv.AddUser in 
                      (select   userid from  dbo.fn_GetOrganizeUser('FB282647-FFE0-440B-81B2-1466D3EAC417')) 
                      and  cv.APDate  between  '" + StartDate + "'  and '" + EndDate + "'and cv.DeptName is not null group by  cv.AddUser,cv.DeptName,cv.CZName )a group by ZjName  order by [PayMoney]  desc ";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }
        /// <summary>
        /// 获取所有个人价值业绩
        /// </summary>        
        /// <returns></returns>
        public DataTable ReturnTableAllPersonValue(string DeptID, string type, DateTime StartDate, DateTime EndDate)
        {
            DataTable dtcustomer = new DataTable();
            string strsql = "";
            strsql += @"select top 20 JlName,SUM(PayMoney) as PayMoney from (select cv.DeptName+cv.CName as JlName , CAST(round(cast(Sum(cv.PayMoneyTotal) as decimal(18,9))/10000,3)  as  numeric(9,2))  as [PayMoney] from V_CrmValueRpt cv
                        where cv.DliverState ='20F825C4-292A-4386-8157-923668BD2682' and cv.CZName<>cv.CName and cv.AddUser in 
                        (select   userid from  dbo.fn_GetOrganizeUser('FB282647-FFE0-440B-81B2-1466D3EAC417')) 
                        and  cv.APDate  between  '" + StartDate + "'  and '" + EndDate + "' and cv.DeptName is not null group by  cv.AddUser,cv.DeptName,cv.CName )a group by JlName  order by [PayMoney]  desc";
            DbSql execsql = new DAL.DBExtend.DbSql();
            dtcustomer = execsql.ExecuteQuery(strsql);
            return dtcustomer;
        }

        /// <summary>
        /// 分公司月度回款明细
        /// </summary>
        /// <param name="deptid"></param>
        /// <param name="dtype"></param>
        /// <param name="sStime"></param>
        /// <param name="sEtime"></param>
        /// <returns></returns>
        public DataTable ReturnRptPayDetailReprot(string deptid,string dtype,string  sStime, string sEtime)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@Dept",SqlDbType.VarChar,100),
                new SqlParameter("@StartDate",SqlDbType.DateTime,1),
                new SqlParameter("@EndDate",SqlDbType.DateTime,1)
             };

            sqlparams[0].Value = deptid;
            sqlparams[1].Value = Convert.ToDateTime(sStime);
            sqlparams[2].Value = Convert.ToDateTime(sEtime);
            DataSet ds = execsql.RunProcedure("sp_GetBranchMayDetail", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        /// <summary>
        /// 获取部门名称
        /// </summary>
        /// <param name="deptid">部门id</param>
        /// <returns></returns>
        public string ReturnDeptNameByDeptid(string deptid)
        {          
            DbSql execsql = new DAL.DBExtend.DbSql();
            string returnstr = string.Empty;
            string strsql = "select DeptName   from DatDepartment   where DeptID = '" + deptid + "'";
             DataTable  dtcustomer = execsql.ExecuteQuery(strsql);
             if (dtcustomer != null)
             {
                 returnstr = dtcustomer.Rows[0]["DeptName"].ToString();
             }
            return returnstr;
        }

        /// <summary>
        /// 分公司月度回款明细
        /// </summary>
        /// <param name="deptid"></param>
        /// <param name="dtype"></param>
        /// <param name="sStime"></param>
        /// <param name="sEtime"></param>
        /// <returns></returns>
        public DataTable ReturnRptcommisionReprot(string deptid, string dtype, string sStime, string sEtime)
        {
            DataTable dt = new DataTable();
            DbSql execsql = new DAL.DBExtend.DbSql();
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@Dept",SqlDbType.VarChar,100),
                new SqlParameter("@StartDate",SqlDbType.DateTime,1),
                new SqlParameter("@EndDate",SqlDbType.DateTime,1)
             };

            sqlparams[0].Value = deptid;
            sqlparams[1].Value = Convert.ToDateTime(sStime);
            sqlparams[2].Value = Convert.ToDateTime(sEtime);
            DataSet ds = execsql.RunProcedure("sp_GetBranchcommisionDetail", sqlparams, true);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
    }
}
